package com.apestech.api.exp;

import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.dictionary.modul.ImportedKey;
import com.apestech.framework.dictionary.modul.PrimaryKey;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.DBUtil;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.xml.table.modul.Tables;
import java.util.ArrayList;

public class ExpImp extends StringUtil {

    private String id;
    private ExpTable exptable;
    private JSONObject impTable;
    SqlSession dsession;
    SqlSession ssession;
    SqlSession sdsession;
    private JSONObject varmap=new JSONObject();
    private ArrayList<String> bak=new ArrayList<String>();

    public ExpImp(String ddatabase, String sdatabase) throws Exception {
        dsession= iniDB(ddatabase);
        ssession= iniDB(sdatabase);
    }

    public ExpImp(String id, String ddatabase, String sdatabase, String sddatabase) throws Exception {
        this.id=id;
        dsession= iniDB(ddatabase);
        ssession= iniDB(sdatabase);
        sdsession= iniDB(sddatabase);
        this.exptable=ExpUtil.getExp(id);
        impTable=new JSONObject();
    }

    public void exec(String tablename) throws Exception {
        if(!dsession.tableExists(tablename)){
            Tables tabs=new Tables();
            String tabremark=ssession.getTableRemark(tablename);
            JSONObject m=new JSONObject();
            m.put("tabName", tablename);
            m.put("tabComment",tabremark);
            tabs.addTabInfo(tablename, m);
            Table tab=ssession.getDictTable(tablename);
            ImportedKey impkey=tab.getPkFKey();
            PrimaryKey pkey=tab.getPkKey();
            JSONObject fields=tab.getField();
            ArrayList<String> klist=fields.getKeylist();
            int len=klist.size();
            for(int i=0; i<len; i++){
                String k=klist.get(i);
                JSONObject map=new JSONObject();
                Table.Field f=tab.getField(k);
                map.put("id", k);
                map.put("type", f.getDbType());
                map.put("notNull", f.getNotNull());
                map.put("defaultValue", f.getColumnDef());
                map.put("comment", f.getRemarks());
                if((","+pkey.getPkColumn()+",").indexOf(","+k+",")>=0){
                    map.put("isKey","true");
                }
                if(impkey!=null){
                    map.put("foreignTab",impkey.getPkTableName(k));
                    map.put("foreignKey",impkey.getColumnName(k));
                }
                tabs.addField(tablename, map);
            }
            tabs.addMasterTab(tablename, tabremark, "1", "1");
            tabs.expain(ssession);
            dsession.getForDDL().createTable(tabs);
        }
        ssession.getForResultSet().table(tablename, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                SqlSession.Var var=dsession.getVar(rs);
                var.setIns("INSERT INTO "+tablename+"("+var.getCol()+") VALUES ("+ var.getVar() +")");
                dsession.getForUpdate().batchUpdate(var.getIns(), rs);
            }
        });
    }

    public void exec() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            exectab(fktables, i);
        }
        if("2".equals(exptable.getSdatabase())){
            insertTable(sdsession, dsession, exptable.getId());
        }
        else insertTable(ssession, dsession, exptable.getId());
    }

    public void exec(int index) throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        exectab(fktables, index);
    }

    public void deleteTable() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            String fid=fktables.get(i).getId();
            operTable(fid, 0);
        }
        operTable(exptable.getId(), 0);
    }

    public void enableFkTable() throws Exception {
        bak.clear();
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            String fid=fktables.get(i).getId();
            try{
                operTable(fid, 1);
            } catch (Exception e) {
                try{
                    saveData(e.getMessage());
                } catch (Exception e1) {
                    throw e1;
                }
            }
            try{
                operTable(fid, 2);
            } catch (Exception e) {
                try{
                    saveData(e.getMessage());
                } catch (Exception e1) {
                    throw e1;
                }
            }
        }
        try{
            operTable(exptable.getId(), 1);
        } catch (Exception e) {
            try{
                saveData(e.getMessage());
            } catch (Exception e1) {
                throw e1;
            }
        }
        try{
            operTable(exptable.getId(), 2);
        } catch (Exception e) {
            try{
                saveData(e.getMessage());
            } catch (Exception e1) {
                throw e1;
            }
        }
    }

    public void disableFkTable() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            String fid=fktables.get(i).getId();
            operTable(fid, 3);
        }
        operTable(exptable.getId(), 3);
    }

    public void disableFkTableReferences() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            String fid=fktables.get(i).getId();
            operTable(fid, 4);
        }
        operTable(exptable.getId(), 4);
    }

    public void deleteBakTable() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            String fid=fktables.get(i).getId();
            operTable(fid, 5);
        }
        operTable(exptable.getId(), 5);
    }

    private void saveData(String msg) throws Exception {
        if(msg.indexOf("未找到父项关键字")>0){
            int p1=msg.indexOf(".");
            int p2=msg.indexOf(")");
            String s=msg.substring(p1+1, p2);
            String sql="SELECT A.CONSTRAINT_NAME, A.TABLE_NAME, A.COLUMN_NAME, C.OWNER,\n" +
                    "        C_PK.TABLE_NAME R_TABLE_NAME, B.COLUMN_NAME R_COLUMN_NAME\n" +
                    "   FROM USER_CONS_COLUMNS A\n" +
                    "   JOIN USER_CONSTRAINTS C ON A.OWNER = C.OWNER\n" +
                    "        AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME\n" +
                    "   JOIN USER_CONSTRAINTS C_PK ON C.R_OWNER = C_PK.OWNER\n" +
                    "        AND C.R_CONSTRAINT_NAME = C_PK.CONSTRAINT_NAME\n" +
                    "   JOIN USER_CONS_COLUMNS B ON C_PK.OWNER = B.OWNER\n" +
                    "        AND C_PK.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.POSITION = A.POSITION\n" +
                    "  WHERE C.CONSTRAINT_TYPE = 'R' AND A.CONSTRAINT_NAME='"+s+"'";
            final String c_name=s;
            dsession.getForResultSet().sql(sql, new QueryCallBackHandler(){
                @Override
                public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                    while(rs.next()){
                        String TABLE_NAME=rs.getString("TABLE_NAME");
                        String R_TABLE_NAME=rs.getString("R_TABLE_NAME");
                        String R_COLUMN_NAME=rs.getString("R_COLUMN_NAME");
                        String COLUMN_NAME=rs.getString("COLUMN_NAME");
                        String tab=TABLE_NAME+"_BAK";
                        String s;
                        String t=tab+"."+COLUMN_NAME;
                        if(!bak.contains(t)) bak.add(t);
                        if(dsession.tableExists(tab)){
                            s=" INSERT INTO "+tab+
                                    " SELECT * FROM "+TABLE_NAME+" WHERE NOT EXISTS(SELECT 1 FROM "+R_TABLE_NAME+" Z WHERE Z."+R_COLUMN_NAME+"="+TABLE_NAME+"."+COLUMN_NAME+")";
                            dsession.getForUpdate().update(s);
                        } else {
                            s=" CREATE TABLE "+tab+" AS "+
                                    " SELECT * FROM "+TABLE_NAME+" WHERE NOT EXISTS(SELECT 1 FROM "+R_TABLE_NAME+" Z WHERE Z."+R_COLUMN_NAME+"="+TABLE_NAME+"."+COLUMN_NAME+")";
                            dsession.getForUpdate().update(s);
                        }
                        s="DELETE "+TABLE_NAME+"  WHERE NOT EXISTS(SELECT 1 FROM "+R_TABLE_NAME+" Z WHERE Z."+R_COLUMN_NAME+"="+TABLE_NAME+"."+COLUMN_NAME+")";
                        dsession.getForUpdate().update(s);
                        dsession.getForDDL().executeDDL("ALTER TABLE "+TABLE_NAME+" ENABLE CONSTRAINT "+c_name);
                    }
                }
            });
        } else throw new Exception(msg);
    }

    private void operTable(String fid, int lx) throws Exception {  //lx: 0删除  1恢复外键 2恢复关联外键 3禁用外键 4禁用关联外键 5删除备份表
        if(fid.indexOf(",")<0){
            operTableLx(fid, lx);
        } else {
            String[] flist=fid.split(",");
            int flen=flist.length;
            for(int f=0; f<flen; f++){
                operTableLx(flist[f], lx);
            }
        }
    }

    private void operTableLx(String fid, int lx) throws Exception {  //lx: 0删除  1恢复外键 2恢复关联外键 3禁用外键 4禁用关联外键
        switch (lx){
            case 0:{
                dsession.getForDDL().truncateTable(fid);
                break;
            }
            case 1:{
                dsession.getForDDL().enableTableFk(fid);
                break;
            }
            case 2:{
                dsession.getForDDL().enableTableFkReferences(fid);
                break;
            }
            case 3:{
                dsession.getForDDL().disableTableFk(fid);
                break;
            }
            case 4:{
                dsession.getForDDL().disableTableFkReferences(fid);
                break;
            }
            case 5:{
                if(dsession.tableExists(fid+"_BAK")){
                    dsession.getForDDL().executeDDL("DROP TABLE "+fid+"_BAK");
                }
                break;
            }
        }
    }

    public void build() throws Exception {
        ArrayList<ExpTable.FkTable> fktables=exptable.getFkTable();
        int len=fktables.size();
        for(int i=0; i<len; i++){
            ExpTable.FkTable tab=fktables.get(i);
            String fid=tab.getId();
            ExpTable ftab=ExpUtil.getExp(fid);
            if("2".equals(ftab.getSdatabase()))
               getTableSql(sdsession, dsession, ftab.getId(), ftab.getSource(), ftab.getSql(), ftab.getField(), ftab.getCheck());
            else getTableSql(ssession, dsession, ftab.getId(), ftab.getSource(), ftab.getSql(), ftab.getField(), ftab.getCheck());
        }
        if("2".equals(exptable.getSdatabase())){
            getTableSql(sdsession, dsession, exptable.getId(), exptable.getSource(), exptable.getSql(), exptable.getField(), exptable.getCheck());
        }
        else getTableSql(ssession, dsession, exptable.getId(), exptable.getSource(), exptable.getSql(), exptable.getField(), exptable.getCheck());
    }

    private void exectab(ArrayList<ExpTable.FkTable> fktables, int index) throws Exception {
        ExpTable.FkTable tab=fktables.get(index);
        String fid=tab.getId();
        ExpTable ftab=ExpUtil.getExp(fid);
        if("2".equals(ftab.getSdatabase())){
            insertTable(sdsession, dsession, ftab.getId());
        }
        else insertTable(ssession, dsession, ftab.getId());
    }

    private void insertTable(SqlSession ssession, SqlSession dsession, String fid) throws Exception {
        String sql=null;
        String ins=null;
        ArrayList<String> sqls = null;
        ArrayList<String> fields=null;
        int pos=fid.indexOf(",");
        if(pos>=0){
            sqls=new ArrayList<String>();
            fields=new ArrayList<String>();
            String[] list=fid.split(",");
            int len=list.length;
            for(int i=0; i<len; i++){
                String tab=list[i];
                SqlSession.Var var= (SqlSession.Var) varmap.get(tab);
                if(i==0) sql=var.getSel();
                sqls.add(var.getIns());
                fields.add(var.getOut());
                dsession.getForDDL().disableTableFk(tab);
                dsession.getForDDL().disableTableFkReferences(tab);
            }
        } else {
            SqlSession.Var var= (SqlSession.Var) varmap.get(fid);
            sql=var.getSel();
            ins=var.getIns();
            dsession.getForDDL().disableTableFk(fid);
            dsession.getForDDL().disableTableFkReferences(fid);
        }
        String finalIns = ins;
        ArrayList<String> finalSqls = sqls;
        ArrayList<String> finalFields = fields;
        ssession.getForResultSet().sql(sql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                if(finalSqls ==null) dsession.getForUpdate().batchUpdate(finalIns, rs);
                else dsession.getForUpdate().batchUpdate(finalSqls, finalFields, rs);
            }
        });
    }

    private void getTableSql(SqlSession ssession, SqlSession dsession, String tablename, String stablename, String sql, JSONObject field, JSONObject check) throws Exception {
        String vsql=sql;
        if(sql==null) vsql=" SELECT * FROM "+tablename+" WHERE 1<>1 ";
        if(field.size()>0){
            ArrayList<String> klist=field.getKeylist();
            int klen=klist.size();
            for(int k=0; k<klen; k++){
                String tab=klist.get(k);
                String fields=((ExpTable.Field)field.get(tab)).getField();
                String out=((ExpTable.Field)field.get(tab)).getOut();
                if(out==null) out=fields;
                info(ssession, dsession, sql, vsql, tab, stablename, check, fields, out);
            }
        } else info(ssession, dsession, sql, vsql, tablename, stablename, check, null, null);
    }

    private void info(SqlSession ssession, SqlSession dsession, String sql, String vsql, String tablename, String stablename, JSONObject check, String fields, String out) throws Exception {
        if(check.size()>0){
            Table tab=ssession.getDictTable(stablename);
            ArrayList<String> klist=check.getKeylist();
            int len=klist.size();
            for(int i=0; i<len; i++){
                String k=klist.get(i);
                ExpTable.Check c= (ExpTable.Check) check.get(k);
                if(!tab.isField(c.getId())){
                    ssession.getForDDL().getAlterAddColumn(stablename, c.getId(), c.getType(), null);
                    if("ROWNUM".equals(c.getDefaultValue())){
                        ssession.getForUpdate().update("UPDATE "+stablename+" SET "+c.getId()+"=ROWNUM");
                    } else {
                        ssession.getForUpdate().update("UPDATE "+stablename+" SET "+c.getId()+"='"+c.getDefaultValue()+"'");
                    }
                }
            }
        }
        ssession.getForResultSet().sql(vsql, new QueryCallBackHandler(){
            @Override
            public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                SqlSession.Var var=null;
                if(fields==null){
                    var=ssession.getVar(rs);
                    var.setIns("INSERT INTO "+tablename+"("+var.getCol()+") VALUES ("+ var.getVar() +")");
                } else {
                    var=new SqlSession.Var();
                    String[] olist=out.split(",");
                    int len=olist.length;
                    String s="";
                    for(int i=0; i<len; i++){
                        if(!"".equals(s)) s=s+",";
                        s=s+"?";
                    }
                    var.setVar(s);
                    var.setIns("INSERT INTO "+tablename+"("+fields+") VALUES ("+ var.getVar() +")");
                    var.setOut(out);
                }

                if(sql==null) var.setSel("SELECT "+var.getCol()+" FROM "+tablename+" WHERE 1=1 ");
                else var.setSel(sql);
                varmap.put(tablename, var);
                ssession.getForResultSet().sql(var.getSel()+" AND 1<>1 ", new QueryCallBackHandler(){
                    @Override
                    public void getData(SqlResultSet srs, JSONArray rslist) throws Exception {
                        getTableStru(dsession, rs, srs, tablename);
                    }
                });
            }
        });
    }

    private void getTableStru(SqlSession dsession, SqlResultSet rs, SqlResultSet srs, String tablename) throws Exception {
        int len=rs.getMetaData().getColumnCount();
        for(int i=1; i<=len; i++){
            String k=rs.getMetaData().getColumnName(i);
            String type1=rs.getMetaData().getColumnTypeName(i);
            String type2=srs.getMetaData().getColumnTypeName(i);
            if(!type1.equals(type2)){
                System.out.println("字段["+k+"]类型有误 源["+type2+"] 目标["+type1+"]");
            } else {
                String str="";
                String type="";
                String ssize="";
                int precision1=rs.getMetaData().getPrecision(i);
                int precision2=srs.getMetaData().getPrecision(i);
                if(precision1<precision2){
                    str="Precision有误 源["+precision2+"] 目标["+precision1+"]";
                    ssize=castToString(precision2);
                }
                int scale1=rs.getMetaData().getScale(i);
                int scale2=srs.getMetaData().getScale(i);
                if(scale1<scale2){
                    if(!"".equals(str)) str=str+" ";
                    str=str+"Scale有误 源["+scale2+"] 目标["+scale1+"]";
                    if(!"".equals(ssize)) ssize=ssize+",";
                    ssize=ssize+castToString(scale2);
                }
                if(!"".equals(str)) {
                    System.out.println("字段["+k+"] "+str);
                    if("VARCHAR2".equals(type1)){
                        type=type1+"("+precision2+" CHAR)";
                    } else{
                        type=type1+"("+ssize+")";
                    }
                    dsession.getForDDL().alterTableColumn(tablename, k, type);
                }
            }
        }
    }

    public ArrayList<String> getBak() {
        return bak;
    }

    private SqlSession iniDB(String db) throws Exception {
        DBUtil.getDataSource(db);
        SqlSession session= DBUtil.getSession(db);
        return session;
    }

}
